package com.xinchuang.mapper.targetSystem;

import com.xinchuang.entity.targetSystem.system.*;
import com.xinchuang.entity.targetSystem.target.TargetData;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * @ClassName SystemMasterMapper
 * @Description: 系统主表
 * @auther xukl
 * @data 2021/3/31  15:03
 * @Version V1.0
 */
public interface SystemMasterMapper {

    /**
     * 查询系统列表
     */
    @Select(value = {
            "<script>",
            "SELECT s_id,system_name,target_type,include,icon ",
            "FROM system_master WHERE task_sign IS NULL ",
            "<if test = 'typeList != null'> ",
            "AND target_type IN (",
            "<foreach collection='typeList' item='item' index='index' separator=','>",
            "#{item}",
            "</foreach>",
            ")",
            "</if>",
            "<if test = 'keyword != null'> AND system_name LIKE '%${keyword}%'</if>",
            " ORDER BY create_time DESC",
            "</script>"
    })
    @Results(value = {
            @Result(property = "sId", column = "s_id"),
            @Result(property = "systemName", column = "system_name"),
            @Result(property = "targetType", column = "target_type", one = @One(select = "com.xinchuang.mapper.targetSystem.external.SystemExternalMapper.selectTypeName")),
            @Result(property = "remark", column = "remark"),
            @Result(property = "iconUrl", column = "icon_url")
    })
    List<SystemMaster> selectSystemList(@Param("typeList") List<String> typeList, @Param("keyword") String keyword);

    /**
     * 查询系统分析关联ID
     */
    @Select(value = {
            "SELECT target_id FROM system_relation WHERE s_id = #{sId}"
    })
    List<String> selectSystemRelationTargetId(@Param("sId") String sId);

    @Select(value = {
            "<script>",
            "SELECT a.target_id FROM ",
            "(SELECT target_id FROM system_relation WHERE s_id = #{sId}) AS a ",
            "JOIN ",
            "(SELECT target_id FROM target_base_data ",
            "<if test = 'country != null'> WHERE country = #{country} </if>",
            ") AS b ",
            "ON ",
            "a.target_id = b.target_id WHERE a.target_id IS NOT NULL",
            "</script>"
    })
    List<String> selectSystemRelationAndCountryTargetId(@Param("sId") String sId, @Param("country") String country);

    /**
     * 查询单个系统
     */
    @Select(value = {
            "SELECT s_id,system_name,target_type,stat,remark,icon,icon_url,user_sign,sort,create_time ",
            "FROM system_master WHERE s_id = #{sId} LIMIT 1"
    })
    @Results(value = {
            @Result(property = "sId", column = "s_id"),
            @Result(property = "systemName", column = "system_name"),
            //one = @One(select = "com.zkwc.targetmanage.dao.external.SystemExternalMapper.selectTypeName")
            @Result(property = "stat", column = "stat"),
            @Result(property = "targetType", column = "target_type"),
            @Result(property = "remark", column = "remark"),
            @Result(property = "iconUrl", column = "icon_url"),
            @Result(property = "userSign", column = "user_sign"),
            @Result(property = "createTime", column = "create_time")
    })
    SystemMaster selectSystemOnly(@Param("sId") String sId);

    /**
     * 查询统计属性
     */
    @Select(value = "SELECT key_value,value,sign FROM info_element WHERE s_id = #{sId}")
    @Results(value = {
            @Result(property = "key", column = "key_value")
    })
    List<InfoElement> selectInfoBySId(@Param("sId") String sId);

    /**
     * 查询选择MB列表
     */
    @Select(value = {
            "<script>",
            "SELECT target_id,target_name,icon ",
            "FROM target_base_data WHERE delete_sign = 1 ",
            "<if test = 'country != null'>",
            " AND country = #{country}",
            "</if>",
            "<foreach collection='classify' item='item' index='index' open=' AND type IN (' separator=',' close=')'>",
            "#{item}",
            "</foreach>",
            "<if test = 'idList != null'> ",
            " AND target_id NOT IN ",
            "<foreach collection='idList' item='item' index='index' open='(' separator=',' close=')'>",
            "#{item}",
            "</foreach>",
            "</if>",
            "<if test = 'keyword != null'>",
            " AND (target_name LIKE '%${keyword}%' OR country LIKE '%${keyword}%')",
            "</if>",
            " ORDER BY create_time DESC",
            "</script>"
    })
    @Results(value = {
            @Result(property = "targetId", column = "target_id"),
            @Result(property = "targetName", column = "target_name"),
            @Result(property = "icon", column = "icon")
    })
    List<TargetData> selectWaitChoiceTarget(@Param("country") String country, @Param("classify") List<String> classify,@Param("idList") List<String> targetIdList,@Param("keyword") String keyword);

    /**
     * 查询体系关联目标信息
     */
    @Select(value = {
            "SELECT a.target_id,a.target_name,a.target_classify,a.icon,a.sqlite_url FROM ",
            "(SELECT target_id,target_name,target_classify,icon,sqlite_url FROM target_base_data WHERE delete_sign = 1) AS a ",
            "JOIN ",
            "(SELECT target_id FROM system_relation WHERE s_id = #{sId}) AS b ",
            "ON ",
            "a.target_id = b.target_id "
    })
    @Results(value = {
            @Result(property = "targetId", column = "target_id"),
            @Result(property = "targetName", column = "target_name"),
            @Result(property = "targetClassify", column = "target_classify"),
            @Result(property = "icon", column = "icon"),
            @Result(property = "sqliteUrl", column = "sqlite_url")
    })
    List<TargetData> selectSystenRelationTargetList(@Param("sId")String sId);

    /**
     * 查询共有属性统计
     */
    @Select(value = {
            "SELECT key_name,key,key_type FROM common_attribute WHERE sign = 1 AND info = #{info}"
    })
    @Results(value = {
            @Result(property = "keyName", column = "key_name"),
            @Result(property = "key", column = "key"),
            @Result(property = "keyType", column = "key_type")
    })
    List<CommonAttribute> findCommonIndex(@Param("info") String info);

    /**
     * 查询私有属性统计
     */
    @Select(value = {
            "SELECT key_name,key,key_type FROM special_attribute ",
            "WHERE type_id = (SELECT target_classify FROM target_base_data WHERE target_id = #{targetId}) AND info = #{info}"
    })
    @Results(value = {
            @Result(property = "keyName", column = "key_name"),
            @Result(property = "key", column = "key"),
            @Result(property = "keyType", column = "key_type")
    })
    List<SpecialAttribute>  findSpecialIndex(@Param("targetId")String targetId, @Param("info") String info);

    /**
     * 查询已选择MB列表
     */
    @Select(value = {
            "<script>",
            "SELECT target_id,target_name,icon ",
            "FROM target_base_data WHERE ",
            "target_id IN ",
            "<foreach collection='idList' item='item' index='index' open='(' separator=',' close=')'>",
            "#{item}",
            "</foreach>",
            "<if test = 'keyword != null'> AND (target_name LIKE '%${keyword}%' OR country LIKE '%${keyword}%')</if>",
            " ORDER BY create_time ASC",
            "</script>"
    })
    @Results(value = {
            @Result(property = "targetId", column = "target_id"),
            @Result(property = "targetName", column = "target_name"),
            @Result(property = "icon", column = "icon")
    })
    List<TargetData> selectChoiceTarget(@Param("idList") List<String> targetIdList, @Param("keyword") String keyword);

    /**
     * 删除系统关联目标
     */
    @Delete(value = "DELETE FROM system_relation WHERE s_id = #{sId}")
    int deleteConfirm(@Param("sId") String sId);

    /**
     * 删除系统关联统计属性
     */
    @Delete(value = "DELETE FROM info_element WHERE s_id = #{sId}")
    int deleteInfo(@Param("sId") String sId);

    /**
     * 新增系统关联
     */
    @Insert(value = {
            "<script>",
            "INSERT INTO system_relation ",
            "(s_rel_id,s_id,target_id) ",
            "VALUES ",
            "<foreach collection='list' item='item' index='index' separator=','>",
            "(#{item.sRelId},#{item.sId},#{item.targetId})",
            "</foreach>",
            "</script>"
    })
    int addSystemRelation(List<SystemRelation> list);

    /**
     * 新增统计属性
     */
    @Insert(value = {
            "<script>",
            "INSERT INTO info_element ",
            "(id,s_id,sys_id,key_value,value,sign) ",
            "VALUES ",
            "<foreach collection='list' item='item' index='index' separator=','>",
            "(#{item.id},#{item.sId},#{item.sysId},#{item.key},#{item.value},#{item.sign})",
            "</foreach>",
            "</script>"
    })
    int addInfoElement(List<InfoElement> list);

    /**
     * 新增系统
     */
    @Insert(value = {
            "INSERT INTO system_master ",
            "(s_id,system_name,target_type,include,remark,user_sign,stat,icon,icon_url,task_sign,create_time) ",
            "VALUES ",
            "(#{sId},#{systemName},#{targetType},#{include},#{remark},#{userSign},#{stat},#{icon},#{iconUrl},#{taskSign},CURRENT_TIMESTAMP)",
            "ON DUPLICATE KEY UPDATE system_name = #{systemName},target_type = #{targetType},include = #{include},remark = #{remark},user_sign = #{userSign},stat = #{stat},icon = #{icon},icon_url = #{iconUrl},task_sign = #{taskSign}"
    })
    int addSystem(SystemMaster systemMaster);

    /**
     * 查询关联目标同类别数量
     */
    @Select(value = {
            "SELECT COUNT(1) FROM system_relation sr ",
            "LEFT JOIN target_base_data td ON sr.target_id = td.target_id ",
            " WHERE td.target_classify = (SELECT target_classify FROM target_base_data WHERE target_id = #{targetId}) ",
            "AND td.delete_sign = 1"
    })
    int selectClassifyCount(@Param("targetId") String targetId);

    @Delete(value = {
            "<script>",
            "DELETE FROM system_relation WHERE s_id = #{s_id} AND ",
            "target_id IN ",
            "<foreach collection='targetList' item='item' index='index' open='(' separator=',' close=')'>",
            "#{item}",
            "</foreach>",
            "</script>"
    })
    int deleteSystemRelation(@Param("s_id") String sId, @Param("targetList") List<String> targetList);

    /**
     * 查询系统列表
     */
    @Select(value = {
            "SELECT key,key_value FROM system_else_table WHERE s_id = #{sId}"
    })
    @Results(value = {
            @Result(property = "key", column = "key"),
            @Result(property = "keyValue", column = "key_value")
    })
    List<SystemElseData> selectSystemElseDataList(@Param("sId") String sId);

    /**
     * 新增系统列表
     */
    @Insert(value = {
            "<script>",
            "INSERT INTO system_else_table ",
            "(id,s_id,key,key_value) ",
            "VALUES ",
            "<foreach collection='list' item='item' index='index' separator=','>",
            "(#{item.id},#{item.sid},#{item.key},#{item.keyValue})",
            "</foreach>",
            "</script>"
    })
    int addSystemElseData(List<SystemElseData> list);

    /**
     * 删除系统列表
     */
    @Delete(value = "DELETE FROM system_else_table WHERE s_id = #{sId}")
    int delSystemElseData(@Param("sId") String sId);

    /**
     * 查询系统列表
     * @param country
     * @param taskSign
     * @return
     */
    @Select(value = {
            "<script>",
            "SELECT s_id,system_name,stat,icon FROM system_master WHERE 1=1 ",
            "<choose>",
            "<when test = 'taksSign != null'>",
            "AND task_sign = #{taksSign} ",
            "</when>",
            "<otherwise>",
            "AND task_sign IS NULL ",
            "</otherwise>",
            "</choose>",
            "<if test = 'country != null'>AND stat = #{country} </if>",
            "<if test = 'include != null'>",
            "<foreach collection='include' item='item' index='index' open=' AND s_id IN (' separator=',' close=')'>",
            "#{item}",
            "</foreach>",
            "</if>",
            "<if test = 'noInclude != null'>",
            "<foreach collection='noInclude' item='item' index='index' open=' AND s_id NOT IN (' separator=',' close=')'>",
            "#{item}",
            "</foreach>",
            "</if>",
            "ORDER BY create_time DESC",
            "</script>"
    })
    @Results(value = {
            @Result(property = "sId", column = "s_id"),
            @Result(property = "systemName", column = "system_name")
    })
    List<SystemMaster> selectSystems(
            @Param("country") String country,
            @Param("taksSign")String taskSign,
            @Param("include") List<String> include,
            @Param("noInclude") List<String> noInclude);

    /**
     * 查询未选择MB列表
     */
    @Select(value = {
            "<script>",
            "SELECT target_id,target_name,icon ",
            "FROM target_base_data WHERE delete_sign = 1 ",
            "<if test = 'idList != null'> ",
            " AND target_id NOT IN ",
            "<foreach collection='idList' item='item' index='index' open='(' separator=',' close=')'>",
            "#{item}",
            "</foreach>",
            "</if>",
            "<if test = 'keyword != null'>",
            " AND (target_name LIKE '%${keyword}%' OR country LIKE '%${keyword}%')",
            "</if>",
            " ORDER BY create_time ASC",
            "</script>"
    })
    @Results(value = {
            @Result(property = "targetId", column = "target_id"),
            @Result(property = "targetName", column = "target_name"),
            @Result(property = "icon", column = "icon")
    })
    List<TargetData> selectTargetList(@Param("idList") List<String> targetIdList,@Param("keyword") String keyword);

    /**
     * 查询未选择MB列表
     */
    @Select(value = {
            "<script>",
            "SELECT target_id,target_name,icon ",
            "FROM target_base_data WHERE delete_sign = 1 ",
            "<if test = 'idList != null'> ",
            " AND target_id IN ",
            "<foreach collection='idList' item='item' index='index' open='(' separator=',' close=')'>",
            "#{item}",
            "</foreach>",
            "</if>",
            "<if test = 'keyword != null'>",
            " AND (target_name LIKE '%${keyword}%' OR country LIKE '%${keyword}%')",
            "</if>",
            " ORDER BY create_time ASC",
            "</script>"
    })
    @Results(value = {
            @Result(property = "targetId", column = "target_id"),
            @Result(property = "targetName", column = "target_name"),
            @Result(property = "icon", column = "icon")
    })
    List<TargetData> selectTargetLists(@Param("idList") List<String> targetIdList,@Param("keyword") String keyword);

    /**
     * 不包含系统内的目标
     * @param sysId
     * @param targetIdList
     * @return
     */
    @Select(value = {
            "<script>",
            "SELECT target_id FROM systems_relation WHERE sys_id = #{sysId} ",
            "<if test = 'idList != null'> ",
            "AND target_id NOT IN ",
            "<foreach collection='idList' item='item' index='index' open='(' separator=',' close=')'>",
            "#{item}",
            "</foreach>",
            "</if>",
            "</script>"
    })
    List<String> selectNoSytemTargetId(@Param("sysId") String sysId,@Param("idList") List<String> targetIdList);

    /**
     *@author zhangxuan
     *@Description TODO 查询与系统无关的数据
     *@Date 16:09 2022/4/1
     *@Param []
     *@return java.util.List<java.lang.String>
     **/
    @Select("SELECT target_id FROM target_base_data WHERE delete_sign = 1 AND target_id NOT IN (SELECT target_id FROM system_relation)")
    List<String> selectNoSystemTargetByNo();

    /**
     * 系统名称查询系统ID
     * @param name
     * @return
     */
    @Select("SELECT s_id FROM system_master WHERE system_name = #{name}")
    String selectSystemName(@Param("name")String name);

    @Select(value = "SELECT s_id AS sId,sort FROM system_master LIMIT 1")
    SystemMaster getOne();

}






